SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[IcIc2Overlap] AS
BEGIN
WITH FullPar AS (
		
		SELECT [caDate].[NAVDate],MAX([caDate].[icExportId]) icExportId, MAX([caDate].[ic2ExportId]) ic2ExportId,SUM([a].[EndAdjAmt]) IcPar, MAX([caIC2Par].[ic2EndAdj]) ic2Par FROM  [FSFundModel].[Reporting].[Asset] a 
				INNER JOIN  [FSFundModel].[Reporting].[Portfolio] p
				ON [a].[ExportId] = [p].[ExportId]
				AND [a].[PortfolioId] = [p].[PortfolioId]

		
				CROSS APPLY( 
				
								SELECT DISTINCT [expIC].[NAVDate], MAX(expic2.ic2ExportId) ic2ExportId, MAX([expIC].[ExportId]) icExportId FROM  [FSFundModel].[Reporting].[Export] expIC
								INNER JOIN  (SELECT DISTINCT [e2].[NAVDate], MAX([e2].[ExportId]) ic2Exportid FROM [FSFundModel].[Reporting].[Export] e2 WHERE [e2].[FundId] = 'FSIC2' AND ([e2].[ExportType] = 'NAV' OR [e2].NAVDate = '12/31/2013')
																						GROUP BY [e2].[NAVDate]) expIc2 ON [expIc2].[NAVDate] = [expIC].[NAVDate]

					
			
								WHERE [expIC].[FundId] = 'FSIC'
								AND ([expIC].[ExportType] = 'NAV' OR [expIC].NAVDate = '12/31/2013')

								GROUP BY [expIC].[NAVDate]
				
				)  caDate
				
				CROSS APPLY(
								SELECT [a2].[ExportId],SUM([a2].[EndAdjAmt]) ic2EndAdj FROM [FSFundModel].[Reporting].[Asset] a2 
								WHERE [a2].[ExportId] = [caDate].[ic2ExportId]
								AND [a2].[IsActiveBeginningOfDay] =1
								GROUP BY [a2].[ExportId]
				) caIC2Par

				WHERE [p].[ExportId] = [caDate].[icExportId] 
				AND [a].[IsActiveEndOfDay] =1
				GROUP BY [caDate].[NAVDate]
) 

SELECT 	[NAVDate]

, [IcPar]
, [ic2Par]
, [Ic2Overlap], [Ic2Overlap]/[ic2Par] AS div FROM  [FullPar] 
CROSS APPLY(
				

				SELECT SUM([a].EndAdjAmt) Ic2Overlap FROM [FSFundModel].[Reporting].[Asset] a 

				CROSS APPLY(

				SELECT DISTINCT [a].[InstrumentId] FROM  [FSFundModel].[Reporting].[Asset] a 
				WHERE [a].[ExportId]  = [FullPar].[icExportId]
				AND [a].[IsActiveEndOfDay] =1 
				INTERSECT
				SELECT DISTINCT [a].[InstrumentId] FROM  [FSFundModel].[Reporting].[Asset] a 
				WHERE [a].[ExportId]  = [FullPar].[ic2ExportId]
				AND [a].[IsActiveEndOfDay] =1
				) caInst

				WHERE [a].[InstrumentId] = [caInst].[InstrumentId]
				AND [a].[ExportId] = [FullPar].[ic2ExportId]
				AND [a].[IsActiveEndOfDay] =1
				GROUP BY [a].[ExportId] 

				) ca2

				WHERE [FullPar].[NAVDate] > '7/19/2013'
				
				ORDER BY [Ic2Overlap]/[ic2Par] ASC
END

GO
